# -*- coding = utf-8 -*-
# @Time : 2025/07/28 10:15
# @Author : 龙王赘婿_彪少
# @File : deal_UI.py
# @Software: PyCharm
# UI界面版本的处理sqlite.db专用

import sys
import pathlib
import sqlite3
import struct
import ast
import pandas as pd
from PyQt5.QtCore import QCoreApplication, QSize, Qt
from PyQt5.QtWidgets import QWidget, QPushButton, QApplication, QLabel, QVBoxLayout, QHBoxLayout, QLineEdit, QFileDialog
from PyQt5.QtGui import QIcon


class Deal_UI(QWidget):
    def __init__(self):
        # 调用父类构造函数
        super().__init__()
        self.init_UI()

    def init_UI(self):
        lab_info = QLabel("龙工专属\n"
                          "点击打开，选择record.db3文件\n"
                          "点击生成，在同级目录下生成CSV文件\n"
                          "1、小时曲线正向有功  decode_tb_0001ff00\n"
                          "2、小时曲线电压     decode_tb_0201ff00\n"
                          "3、小时曲线电流     decode_tb_0202ff00\n"
                          "4、小时曲线功率因素  decode_tb_0206ff00\n", self)
        lab_info.setAlignment(Qt.AlignCenter)

        self.line_file = QLineEdit("文件路径", self)

        btn_open = QPushButton("打开", self)
        btn_open.setToolTip("请选择对应record.db3文件")
        btn_open.resize(btn_open.sizeHint())
        btn_open.clicked.connect(self.slot_open_file)

        btn_make = QPushButton("生成", self)
        btn_make.setToolTip("生成的CSV文件在同目录下")
        btn_make.resize(btn_make.sizeHint())
        btn_make.clicked.connect(self.slot_make_file)

        lay_h = QHBoxLayout()
        lay_h.addWidget(btn_open)
        lay_h.addWidget(btn_make)

        lay_v = QVBoxLayout()
        lay_v.addWidget(lab_info)
        lay_v.addWidget(self.line_file)
        lay_v.addLayout(lay_h)
        self.setLayout(lay_v)

        self.resize(QSize(500, 300))
        self.setWindowTitle("龙工专用解析record.db3")
        self.setWindowIcon(QIcon('favicon.ico'))
        self.show()

    def slot_open_file(self):
        file_path, _ = QFileDialog.getOpenFileName(
            self,
            "选择record.db3文件",
            "./",
            "sqlite.db3 (*.db3);;所有文件 (*)"
        )
        self.line_file.setText(file_path)
        print(file_path)

    def slot_make_file(self):
        db_path = pathlib.Path(self.line_file.text())
        print(db_path)
        self.export_db_to_csv(db_path)
        csv_path = db_path.with_name("tb_0001ff00.csv")
        self.decode_one_file(csv_path, 8)
        csv_path = db_path.with_name("tb_0201ff00.csv")
        self.decode_one_file(csv_path, 4)
        csv_path = db_path.with_name("tb_0202ff00.csv")
        self.decode_one_file(csv_path, 4)
        csv_path = db_path.with_name("tb_0206ff00.csv")
        self.decode_one_file(csv_path, 4)

    def export_db_to_csv(self, db_path: pathlib.Path) -> None:
        """把数据库里的每张表导出成 CSV"""
        if not db_path.exists():
            print(f"❌ 找不到数据库文件: {db_path.resolve()}")
            sys.exit(1)

        conn = sqlite3.connect(db_path)
        # 读取所有表名
        tables = pd.read_sql_query("SELECT name FROM sqlite_master WHERE type='table'", conn)["name"]

        if tables.empty:
            print("⚠️  数据库里没有表，退出。")
            return

        for tbl in tables:
            if tbl == 'tb_0001ff00' or tbl == 'tb_0201ff00' or tbl == 'tb_0202ff00' or tbl == 'tb_0206ff00':
                df = pd.read_sql_query(f"SELECT * FROM [{tbl}]", conn)
                csv_file = db_path.with_name(f"{tbl}.csv")
                df.to_csv(csv_file, index=False, encoding="utf-8")
                print(f"✅ {tbl} -> {csv_file.name}  ({len(df)} 行)")

        conn.close()

    def decode_one_file(self, csv_path: pathlib.Path, type):
        """处理单个文件"""
        df = pd.read_csv(csv_path)
        print(df.columns)
        if "DataSet" not in df.columns:
            print(f"⚠️ 跳过 {csv_path}：缺少 DataSet 列")
            return

        # 字符串 b'...' 转 bytes
        df["DataSet"] = df["DataSet"].apply(lambda s: ast.literal_eval(s))

        # 逐行解码，跳过不合法行
        decoded_rows = []
        skip_count = 0
        for blob in df["DataSet"]:
            if type == 4:
                floats = self.decode_4n_safe(blob)
                if not floats:
                    skip_count += 1
                decoded_rows.append(floats)
            elif type == 8:
                floats = self.decode_8n_safe(blob)
                if not floats:
                    skip_count += 1
                decoded_rows.append(floats)

        if skip_count == len(df):
            print(f"⚠️ 跳过 {csv_path}：所有行 DataSet 长度均非 4 字节倍数")
            return

        # 构造浮点 DataFrame
        float_df = pd.DataFrame(decoded_rows)
        float_df.columns = [f"f{i}" for i in float_df.columns]

        # 保留三位小数
        float_df = float_df.round(3)

        # 横向拼接（保留原 DataSet）
        out = pd.concat([df, float_df], axis=1)

        # 保存
        out_name = csv_path.with_name("decode_" + csv_path.name)
        out.to_csv(out_name, index=False, encoding="utf-8")
        print(
            f"✅ {csv_path} → {out_name}  "
            f"({len(df)} 行, 跳过 {skip_count} 行, 输出 {out.shape[1]} 列)"
        )

    def decode_4n_safe(self, blob: bytes):
        """
        把 4*n 字节数据解码成 n 个 float；
        若长度不是 4 的倍数，返回空列表（表示跳过该行）。
        """
        if len(blob) % 4:
            return []
        return list(struct.unpack(f'<{len(blob) // 4}f', blob))

    def decode_8n_safe(self, blob: bytes):
        """
        把 8*n 字节数据解码成 n 个 float；
        若长度不是 8 的倍数，返回空列表（表示跳过该行）。
        """
        if len(blob) % 8:
            return []
        return list(struct.unpack(f'<{len(blob) // 8}d', blob))

if __name__ == '__main__':
    app = QApplication(sys.argv)
    ui = Deal_UI()
    sys.exit(app.exec_())